/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.app.dao;

import edu.app.persistence.Recette;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author esprit
 */
public class RecetteDAO extends IDAO<Recette> {

    @Override
    public Recette find(String nom) {
        String sql = "SELECT * FROM recette WHERE nom=?";
        Recette found = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, nom);
            resultSet = preparedStatement.executeQuery(); 
            System.out.println(sql);
            if (resultSet.next()) {
              /**/  found = new Recette( resultSet.getInt("id"),resultSet.getString("nom"), resultSet.getString("categorie"), resultSet.getString("ingredients"), resultSet.getInt("duree"), resultSet.getFloat("note"), resultSet.getInt("favorie"), resultSet.getString("remarques"), resultSet.getBytes("image"),resultSet.getFloat("cout"), resultSet.getInt("nbr_personne"), resultSet.getString("origine"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "find recette failed", ex);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
            }
        }
        return found;
    }
    

    @Override
    public void create(Recette recette) {
        if (find(recette.getNom()) == null) {
            
            String sql = "INSERT INTO recette (nom,categorie,ingredients,duree,note,favorie,remarques,image,cout,nbr_personne,origine) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, recette.getNom());
                preparedStatement.setString(2, recette.getCategorie());
                preparedStatement.setString(3, recette.getIngredient());
                preparedStatement.setInt(4, recette.getDuree());
                preparedStatement.setFloat(5, recette.getNote());
                preparedStatement.setInt(6, recette.getFavorie());
                preparedStatement.setString(7, recette.getRemarques());
                preparedStatement.setBytes(8, recette.getImage());
                preparedStatement.setFloat(9, recette.getCout());
                preparedStatement.setInt(10, recette.getNbr_personne());
                preparedStatement.setString(11, recette.getOrigine());
                preparedStatement.executeUpdate(); 
                System.out.println(sql);
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "insert failed", ex);
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
                }
            }
        }    

    }

    @Override
    public void update(Recette recette) {
        if (find(recette.getNom()) != null) {

            String sql = "UPDATE recette SET nom=?,categorie=?,ingredients=?,duree=?,note=?,favorie=?,remarques=?,image=?,cout=?,nbr_personne=?,origine=? WHERE nom=?";
            PreparedStatement preparedStatement = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
               preparedStatement.setString(1, recette.getNom());
                preparedStatement.setString(2, recette.getCategorie());
                preparedStatement.setString(3, recette.getIngredient());
                preparedStatement.setInt(4, recette.getDuree());
                preparedStatement.setFloat(5, recette.getNote());
                preparedStatement.setInt(6, recette.getFavorie());
                preparedStatement.setString(7, recette.getRemarques());
                preparedStatement.setBytes(8, recette.getImage());
                preparedStatement.setFloat(9, recette.getCout());
                preparedStatement.setInt(10, recette.getNbr_personne());
                preparedStatement.setString(11, recette.getOrigine());
                preparedStatement.setString(12, recette.getNom());
                preparedStatement.executeUpdate();
                System.out.println(sql);
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "update failed", ex);
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
                }
            }
        }
    }

    @Override
    public void delete(Recette recette) {
        if (find(recette.getNom()) != null) {

            String sql = "DELETE FROM recette WHERE nom=?";
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, recette.getNom());
                preparedStatement.executeUpdate();
                                                  
                System.out.println(sql);
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "delete failed", ex);
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
                }
            }

        }
    }

    @Override
    public List<Recette> findAll(String ordre) {
        List<Recette> list = new ArrayList<Recette>();
        String sql = "SELECT * FROM recette "+ordre;
        Statement statement = null; 
        ResultSet resultSet = null; 
        
        try {
            statement = connection.createStatement(); 
            resultSet = statement.executeQuery(sql);  
            System.out.println(sql);
            while (resultSet.next()) {
                list.add(new Recette(resultSet.getInt("id"),resultSet.getString("nom"), resultSet.getString("categorie"), resultSet.getString("ingredients"), resultSet.getInt("duree"), resultSet.getFloat("note"), resultSet.getInt("favorie"), resultSet.getString("remarques"), resultSet.getBytes("image"),resultSet.getFloat("cout"), resultSet.getInt("nbr_personne"), resultSet.getString("origine")));
            }


        } catch (SQLException ex) {
            Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "find all employees failed", ex);
        } finally {            
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
            }
        }
        return list;
    }

    @Override
    public List<Recette> findAllDesserts(String ordre) {
       List<Recette> list = new ArrayList<Recette>();
        String sql = "SELECT * FROM recette where upper(categorie)='DESSERT' "+ordre;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            System.out.println(sql);
            while (resultSet.next()) {
                list.add(new Recette( resultSet.getInt("id"),resultSet.getString("nom"), resultSet.getString("categorie"), resultSet.getString("ingredients"), resultSet.getInt("duree"), resultSet.getFloat("note"), resultSet.getInt("favorie"), resultSet.getString("remarques"), resultSet.getBytes("image"),resultSet.getFloat("cout"), resultSet.getInt("nbr_personne"), resultSet.getString("origine")));
            }


        } catch (SQLException ex) {
            Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "find all employees failed", ex);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
            }
        }
        return list;
    
}

   

    @Override
    public List<Recette> findAllPlats(String ordre) {
         List<Recette> list = new ArrayList<Recette>();
        String sql = "SELECT * FROM recette where upper(categorie)='PLAT' "+ordre;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            System.out.println(sql);
            while (resultSet.next()) {
                list.add(new Recette( resultSet.getInt("id"),resultSet.getString("nom"), resultSet.getString("categorie"), resultSet.getString("ingredients"), resultSet.getInt("duree"), resultSet.getFloat("note"), resultSet.getInt("favorie"), resultSet.getString("remarques"), resultSet.getBytes("image"),resultSet.getFloat("cout"), resultSet.getInt("nbr_personne"), resultSet.getString("origine")));
            }


        } catch (SQLException ex) {
            Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "find all employees failed", ex);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
            }
        }
        return list;
    }

   

    @Override
    public List<Recette> findAllEntrees(String ordre) {
       List<Recette> list = new ArrayList<Recette>();
        String sql = "SELECT * FROM recette where upper(categorie)='ENTREE' "+ordre;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            System.out.println(sql);
            while (resultSet.next()) {
                list.add(new Recette( resultSet.getInt("id"),resultSet.getString("nom"), resultSet.getString("categorie"), resultSet.getString("ingredients"), resultSet.getInt("duree"), resultSet.getFloat("note"), resultSet.getInt("favorie"), resultSet.getString("remarques"), resultSet.getBytes("image"),resultSet.getFloat("cout"), resultSet.getInt("nbr_personne"), resultSet.getString("origine")));
            }


        } catch (SQLException ex) {
            Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "find all employees failed", ex);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(RecetteDAO.class.getName()).log(Level.SEVERE, "free resourses failed", ex);
            }
        }
        return list;
    }

    
}
